# -*- coding: utf-8 -*-
"""
Version: 0.1
Author: CarpeDiem
Date: 2022/9/6
Description: 完成一个GUI版本的停车场管理信息
"""

import tkinter as tk
import tkinter.messagebox
from tkinter.ttk import Treeview
import pymysql
import time
import math
'''建立数据库'''
conn = pymysql.connect(host='localhost',
                       user='root',
                       password='root',
                       database='parking_lot_management',
                       charset="utf8")
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS parking_lot")
sql = """
CREATE TABLE Parking_lot (
    id int(8) NOT NULL AUTO_INCREMENT,
    姓名 varchar(50) NOT NULL,
    车牌号码 varchar(50) NOT NULL,
    手机号码 varchar(50) NOT NULL,
    停车位编号 varchar(50) NOT NULL,
    停车位状态 varchar(50) NOT NULL,
    到达时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    离开时间 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    停车时长 decimal(4,2) DEFAULT NULL,
    总计收费 decimal(4,2) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
"""
cursor.execute(sql)  # 执行SQL语句
conn.close()  # 关闭数据库连接
'''根窗口'''
root = tk.Tk()
root.title("停车场管理系统")
root.geometry("1200x675")
root.configure(bg="white")
root.resizable(width=False, height=False)  # 设置为不可拉伸

frame = tk.Frame(root, bg="white")
frame.place(width=900, height=600)

canvas = tk.Canvas(root, bg="white")
canvas.place(width=960, height=600)
canvas.create_text(450 + 3,
                   180 + 2.3,
                   text="欢 迎 使 用",
                   font=("黑体", 70),
                   fill='grey')  # 关键：这行代码要放在下一行的前面！
canvas.create_text(450, 180, text="欢 迎 使 用", font=("黑体", 70), fill='black')
canvas.create_text(450 + 3,
                   340 + 2.3,
                   text="停车场管理系统",
                   font=("黑体", 65),
                   fill='grey')  # 关键：这行代码要放在下一行的前面！
canvas.create_text(450, 340, text="停车场管理系统", font=("黑体", 65), fill='black')


def duration(Entrance, Exit):
    '''停车时长函数'''
    timeArray1 = time.strptime(Entrance, "%Y-%m-%d %H:%M:%S")
    time_entrance = time.mktime(timeArray1)
    timeArray2 = time.strptime(Exit, "%Y-%m-%d %H:%M:%S")
    time_exit = time.mktime(timeArray2)
    parking_time = round((time_exit - time_entrance) / 3600, 2)  # 单位转化为小时
    return parking_time


def charing(time):
    '''
    停车场收费函数
    time 单位为小时
    规则：
    1. 半小时内免费
    2. 收费标准：5元/小时, 不足1h按1h计算
    3. 24小时内最高收费100元
    4. 超过24h按上述标准重新计算
    '''
    if time < 0.5:
        cost = 0
    elif 0.5 < time <= 20:
        cost = 5 * math.ceil(time)
    elif 20 < time <= 24:
        cost = 100
    else:
        cost = 100 * (time // 24) + 5 * math.ceil(time - time // 24 * 24)
    return cost


'''车辆管理'''


def CarManagement():
    frame_CarManagement = tk.Frame(root, bg="white")
    frame_CarManagement.place(width=900, height=600)

    label_register = tk.Label(frame_CarManagement,
                              text="停车场车辆登记",
                              font=("黑体", 25),
                              bg="white",
                              pady=20)
    label_register.pack()
    label_entry = tk.Label(frame_CarManagement,
                           text="汽车驶入",
                           font=("黑体", 18),
                           bg="white",
                           pady=3)
    label_entry.pack()
    label_exit = tk.Label(frame_CarManagement,
                          text="汽车驶出",
                          font=("黑体", 18),
                          bg="white",
                          pady=180)
    label_exit.pack()

    label_name1 = tk.Label(frame_CarManagement,
                           text="姓名:",
                           font=("黑体", 13),
                           bg="white")
    label_name1.place(relx=0.1, rely=0.23)
    name_var1 = tk.StringVar()
    name1 = tk.Entry(frame_CarManagement,
                     textvariable=name_var1,
                     width=17,
                     bd=2,
                     bg="white",
                     font="黑体 13")
    name1.place(relx=0.23, rely=0.23)

    label_car_number1 = tk.Label(frame_CarManagement,
                                 text="车牌号码:",
                                 font=("黑体", 13),
                                 bg="white")
    label_car_number1.place(relx=0.58, rely=0.23)
    number_var1 = tk.StringVar()
    car_number1 = tk.Entry(frame_CarManagement,
                           textvariable=number_var1,
                           width=17,
                           bd=2,
                           bg="white",
                           font="黑体 13")
    car_number1.place(relx=0.74, rely=0.23)

    label_phone1 = tk.Label(frame_CarManagement,
                            text="手机号码:",
                            font=("黑体", 13),
                            bg="white")
    label_phone1.place(relx=0.1, rely=0.33)
    phone_var1 = tk.StringVar()
    phone1 = tk.Entry(frame_CarManagement,
                      textvariable=phone_var1,
                      width=17,
                      bd=2,
                      bg="white",
                      font="黑体 13")
    phone1.place(relx=0.23, rely=0.33)

    label_park_number1 = tk.Label(frame_CarManagement,
                                  text="停车位编号:",
                                  font=("黑体", 13),
                                  bg="white")
    label_park_number1.place(relx=0.58, rely=0.33)
    park_number_var1 = tk.StringVar()
    park_number1 = tk.Entry(frame_CarManagement,
                            textvariable=park_number_var1,
                            width=17,
                            bd=2,
                            bg="white",
                            font="黑体 13")
    park_number1.place(relx=0.74, rely=0.33)

    def entry():
        '''汽车驶入停车场信息登记'''
        if name_var1.get() != '' and number_var1.get(
        ) != '' and phone_var1.get() != '' and park_number_var1.get() != '':
            a = str(park_number_var1.get())
            if a.isdigit() and 0 < int(a) <= 100:
                # 打开数据库连接
                db1 = pymysql.connect(host='localhost',
                                      user='root',
                                      password='hy1999721',
                                      database='parkinglotmanagement',
                                      charset="utf8")
                cursor1 = db1.cursor()  # 使用cursor()方法获取操作
                try:
                    flag = 0
                    cursor1.execute("select * from parking_lot")
                    for row in cursor1.fetchall():
                        if row[2] == number_var1.get():
                            flag = 1
                            tk.messagebox.showerror(title='警告',
                                                    message='该车已进入停车场！')
                            break
                        if row[4] == park_number_var1.get(
                        ) and row[5] == 'FULL':
                            flag = 1
                            tk.messagebox.showerror(title='警告',
                                                    message='该车位已被占用！')
                            break
                    if flag == 0:
                        judge = "FULL"
                        park = (name_var1.get(), number_var1.get(),
                                phone_var1.get(), park_number_var1.get(),
                                judge)
                        cursor1.execute(
                            "insert into parking_lot(姓名, 车牌号码, 手机号码, 停车位编号, 停车位状态) \
                                        values(%s, %s, %s, %s, %s)",
                            park)  # 执行SQL语句
                        db1.commit()  # 提交到数据库执行
                        tk.messagebox.showinfo(title='提示',
                                               message='您已成功进入停车场！')
                except:
                    db1.rollback()  # 发生错误时回滚
                    tk.messagebox.showerror(title='警告', message='数据库连接失败！')
                db1.close()  # 关闭数据库连接
            else:
                tk.messagebox.showerror(title='警告', message='车位有误！')
        else:
            tk.messagebox.showerror(title='警告', message='请完整填写相关信息！')

    button1 = tk.Button(frame_CarManagement,
                        text="确认",
                        font=("黑体", 12),
                        bg="white",
                        bd=2,
                        width=8,
                        command=entry)
    button1.place(relx=0.72, rely=0.43)

    def clear_entrance():
        '''清空文本框中的内容'''
        name1.delete(0, 'end')
        car_number1.delete(0, 'end')
        phone1.delete(0, 'end')
        park_number1.delete(0, 'end')

    button2 = tk.Button(frame_CarManagement,
                        text="清空",
                        font=("黑体", 12),
                        bg="white",
                        bd=2,
                        width=8,
                        command=clear_entrance)
    button2.place(relx=0.84, rely=0.43)

    label_car2 = tk.Label(frame_CarManagement,
                          text="车牌号码:",
                          font=("黑体", 13),
                          bg="white")
    label_car2.place(relx=0.3, rely=0.58)
    car_var2 = tk.StringVar()
    car2 = tk.Entry(frame_CarManagement,
                    textvariable=car_var2,
                    width=19,
                    bd=2,
                    bg="white",
                    font="黑体 13")
    car2.place(relx=0.45, rely=0.58)

    label_park_time2 = tk.Label(frame_CarManagement,
                                text="停车时长:",
                                font=("黑体", 13),
                                bg="white")
    label_park_time2.place(relx=0.3, rely=0.68)
    time_var2 = tk.StringVar()
    park_time2 = tk.Entry(frame_CarManagement, textvariable=time_var2, width=19, bd=2, bg="white", font="黑体 13",\
                         readonlybackground="white")
    park_time2.config(state='readonly')
    park_time2.place(relx=0.45, rely=0.68)

    label_charge2 = tk.Label(frame_CarManagement,
                             text="总计收费:",
                             font=("黑体", 13),
                             bg="white")
    label_charge2.place(relx=0.3, rely=0.78)
    charge_var2 = tk.StringVar()
    charge2 = tk.Entry(frame_CarManagement, textvariable=charge_var2, width=19, bd=2, bg="white", font="黑体 13",\
                      readonlybackground="white")
    charge2.config(state='readonly')
    charge2.place(relx=0.45, rely=0.78)

    def Exit():
        if car_var2.get() != '':
            # 打开数据库连接
            db2 = pymysql.connect(host='localhost', user='root', password='hy1999721', database='parkinglotmanagement',\
                                  charset="utf8")
            cursor2 = db2.cursor()  # 使用cursor()方法获取操作游标
            try:
                cursor2.execute("select * from parking_lot")
                n = 1
                for row in cursor2.fetchall():
                    if row[2] == car_var2.get():
                        if row[5] == "FULL":
                            n = 0
                            time_entry = str(row[6])
                            time_exit = time.strftime("%Y-%m-%d %H:%M:%S",
                                                      time.localtime())
                            totaltime = duration(time_entry, time_exit)
                            money = charing(totaltime)
                            time_var2.set(str(totaltime))
                            charge_var2.set(str(money))
                            cursor2.execute(
                                "update parking_lot set 停车位状态 = %s where 车牌号码 = %s",
                                ('FALSE', row[2]))
                            cursor2.execute(
                                "update parking_lot set 停车时长 = %s where 车牌号码 = %s",
                                (str(totaltime), row[2]))
                            cursor2.execute(
                                "update parking_lot set 总计收费 = %s where 车牌号码 = %s",
                                (str(money), row[2]))
                            tk.messagebox.showinfo(title='提示',
                                                   message='您已成功驶出停车场！祝您一路顺风！')
                            break
                        else:
                            tk.messagebox.showerror(title='警告',
                                                    message='该车已离开停车场！')
                if n == 1:
                    tk.messagebox.showerror(title='警告',
                                            message='未找到符合要求的车辆，请确认后输入！')
            except:
                db2.rollback()  # 发生错误时回滚
                tk.messagebox.showerror(title='警告', message='数据库连接失败！')
            db2.close()  # 关闭数据库连接
        else:
            tk.messagebox.showerror(title='警告', message='请完整填写相关信息！')

    button3 = tk.Button(frame_CarManagement,
                        text="确认",
                        font=("黑体", 12),
                        bg="white",
                        bd=2,
                        width=8,
                        command=Exit)
    button3.place(relx=0.72, rely=0.85)

    def clear_exit():
        '''清空文本框中的内容'''
        car2.delete(0, 'end')
        time_var2.set('')
        charge_var2.set('')

    button4 = tk.Button(frame_CarManagement,
                        text="清空",
                        font=("黑体", 12),
                        bg="white",
                        bd=2,
                        width=8,
                        command=clear_exit)
    button4.place(relx=0.84, rely=0.85)


'''信息查询'''


def InformationSearch():
    frame_InformationSearch = tk.Frame(root, bg="white")
    frame_InformationSearch.place(width=900, height=600)

    label_register = tk.Label(frame_InformationSearch,
                              text="停车场车辆信息查询",
                              font=("黑体", 25),
                              bg="white",
                              pady=20)
    label_register.pack()

    label_car_number = tk.Label(frame_InformationSearch,
                                text="车牌号码:",
                                font=("黑体", 17, "bold"),
                                bg="white")
    label_car_number.place(relx=0.24, rely=0.175)
    car_var = tk.StringVar()
    car_number = tk.Entry(frame_InformationSearch,
                          textvariable=car_var,
                          width=20,
                          bd=2,
                          bg="white",
                          font="黑体 16")
    car_number.place(relx=0.39, rely=0.178)

    label_name = tk.Label(frame_InformationSearch,
                          text="姓名:",
                          font=("黑体", 15),
                          bg="white")
    label_name.place(relx=0.1, rely=0.35)
    name_var = tk.StringVar()
    name = tk.Entry(frame_InformationSearch, textvariable=name_var, width=17, bd=2, bg="white", font="黑体 15",\
                    readonlybackground="white")
    name.config(state='readonly')
    name.place(relx=0.22, rely=0.35)

    label_phone = tk.Label(frame_InformationSearch,
                           text="手机号码:",
                           font=("黑体", 15),
                           bg="white")
    label_phone.place(relx=0.56, rely=0.35)
    phone_var = tk.StringVar()
    phone = tk.Entry(frame_InformationSearch, textvariable=phone_var, width=17, bd=2, bg="white", font="黑体 15",\
                     readonlybackground="white")
    phone.config(state='readonly')
    phone.place(relx=0.7, rely=0.35)

    _label_car = tk.Label(frame_InformationSearch,
                          text="车牌号码:",
                          font=("黑体", 15),
                          bg="white")
    _label_car.place(relx=0.1, rely=0.49)
    _car_var = tk.StringVar()
    _car = tk.Entry(frame_InformationSearch, textvariable=_car_var, width=17, bd=2, bg="white", font="黑体 15",\
                    readonlybackground="white")
    _car.config(state='readonly')
    _car.place(relx=0.22, rely=0.49)

    label_park = tk.Label(frame_InformationSearch,
                          text="停车编号:",
                          font=("黑体", 15),
                          bg="white")
    label_park.place(relx=0.56, rely=0.49)
    park_var = tk.StringVar()
    park = tk.Entry(frame_InformationSearch, textvariable=park_var, width=17, bd=2, bg="white", font="黑体 15",\
                    readonlybackground="white")
    park.config(state='readonly')
    park.place(relx=0.7, rely=0.49)

    label_arrival_time = tk.Label(frame_InformationSearch,
                                  text="到达时间:",
                                  font=("黑体", 15),
                                  bg="white")
    label_arrival_time.place(relx=0.1, rely=0.63)
    arrival_time_var = tk.StringVar()
    arrival_time = tk.Entry(frame_InformationSearch, textvariable=arrival_time_var, width=19, bd=2, bg="white",\
                            font="黑体 15", readonlybackground="white")
    arrival_time.config(state='readonly')
    arrival_time.place(relx=0.22, rely=0.63)

    label_current_time = tk.Label(frame_InformationSearch,
                                  text="当前时间:",
                                  font=("黑体", 15),
                                  bg="white")
    label_current_time.place(relx=0.56, rely=0.63)
    current_time_var = tk.StringVar()
    current_time = tk.Entry(frame_InformationSearch, textvariable=current_time_var, width=19, bd=2, bg="white",\
                            font="黑体 15", readonlybackground="white")
    current_time.config(state='readonly')
    current_time.place(relx=0.7, rely=0.63)

    def search():
        if car_var.get() != '':
            # 打开数据库连接
            db3 = pymysql.connect(host='localhost', user='root', password='hy1999721', database='parkinglotmanagement',\
                                  charset="utf8")
            cursor3 = db3.cursor()  # 使用cursor()方法获取操作游标
            try:
                cursor3.execute("select * from parking_lot")
                n = 1
                for row in cursor3.fetchall():
                    if row[2] == car_var.get():
                        n = 0
                        name_var.set(row[1])
                        phone_var.set(row[3])
                        _car_var.set(row[2])
                        park_var.set(row[4])
                        arrival_time_var.set(row[6])
                        current_time_var.set(
                            time.strftime("%Y-%m-%d %H:%M:%S",
                                          time.localtime()))
                        break
                if n == 1:
                    tk.messagebox.showerror(title='警告',
                                            message='未找到符合要求的车辆，请重新确认后再输入！')
            except:
                db3.rollback()  # 发生错误时回滚
                tk.messagebox.showerror(title='警告', message='数据库连接失败！')
            db3.close()  # 关闭数据库连接
        else:
            tk.messagebox.showerror(title='警告', message='请完整填写相关信息！')

    button_search = tk.Button(frame_InformationSearch, text="搜索查询", font=("黑体", 13), bg="white", bd=2, width=8,\
                              command=search)
    button_search.place(relx=0.68, rely=0.175)

    def clear_search():
        '''清空文本框中的内容'''
        car_number.delete(0, 'end')
        name_var.set('')
        phone_var.set('')
        _car_var.set('')
        park_var.set('')
        arrival_time_var.set('')
        current_time_var.set('')

    button_clear = tk.Button(frame_InformationSearch, text="清空", font=("黑体", 13), bg="white", bd=2, width=8,\
                             command=clear_search)
    button_clear.place(relx=0.8, rely=0.8)


''''当前停车场信息'''


def ParkingInformation():
    frame_ParkingInformation = tk.Frame(root, bg="white")
    frame_ParkingInformation.place(width=900, height=600)

    label_register = tk.Label(frame_ParkingInformation,
                              text="当前停车场信息查询",
                              font=("黑体", 25),
                              bg="white",
                              pady=20)
    label_register.pack()

    label_total_number = tk.Label(frame_ParkingInformation,
                                  text="停车场车位总数",
                                  font=("黑体", 20),
                                  bg="white")
    label_total_number.place(relx=0.39, rely=0.17)

    total_number = tk.Label(frame_ParkingInformation,
                            text="100",
                            font=("DS-digital", 55),
                            bg="white")
    total_number.place(relx=0.45, rely=0.27)

    label_surplus_number = tk.Label(frame_ParkingInformation,
                                    text="停车场当前可用车位数",
                                    font=("黑体", 20),
                                    bg="white")
    label_surplus_number.place(relx=0.35, rely=0.45)

    def getnumber():
        db4 = pymysql.connect(host='localhost', user='root', password='hy1999721', database='parkinglotmanagement', \
                              charset="utf8")
        cursor4 = db4.cursor()  # 使用cursor()方法获取操作游标
        try:
            used = 0
            cursor4.execute("select * from parking_lot")
            for row in cursor4.fetchall():
                if row[5] == 'FULL':
                    used += 1
            not_used = str(100 - used)
            remainder.set(not_used)
        except:
            db4.rollback()  # 发生错误时回滚
            tk.messagebox.showerror(title='警告', message='数据库连接失败！')
        db4.close()  # 关闭数据库连接
        frame_ParkingInformation.after(1000,
                                       getnumber)  # 每隔1s调用函数getnumber获取剩余车位数

    remainder = tk.StringVar()
    surplus_number = tk.Label(frame_ParkingInformation,
                              textvariable=remainder,
                              font=("DS-digital", 55),
                              bg="white")
    surplus_number.place(relx=0.45, rely=0.55)
    getnumber()

    def parking_place():
        window = tk.Toplevel()
        window.title("车位详细信息")
        window.configure(bg="white")
        window.geometry("400x500")
        window.resizable(width=False, height=False)  # 设置为不可拉伸
        lable = tk.Label(window,
                         text="车位详细信息",
                         font=("黑体", 20),
                         bg="white",
                         pady=20)
        lable.pack()

        cols = ("c1", "c2")
        scrollBar = tk.Scrollbar(window, orient='vertical')  # 竖直滚动条
        scrollBar.pack(side='right', fill='y')
        tree = Treeview(window,
                        height=19,
                        show='headings',
                        columns=cols,
                        yscrollcommand=scrollBar.set)
        # 设置每列宽度和对齐方式
        tree.column("c1", width=180, anchor='center')  # 每一行的宽度, 居中放置
        tree.column("c2", width=190, anchor='center')
        # 设置每列表头标题文本
        tree.heading("c1", text="停车位编号")
        tree.heading("c2", text="车位状态")

        # 连接数据库
        db5 = pymysql.connect(host='localhost', user='root', password='hy1999721', database='parkinglotmanagement', \
                              charset="utf8")
        cursor5 = db5.cursor()  # 使用cursor()方法获取操作游标
        try:
            state = ['空闲'] * 100
            cursor5.execute("select * from parking_lot")
            for row in cursor5.fetchall():
                if row[5] == 'FULL':
                    state[int(row[4]) - 1] = '已被' + row[2] + '占用'
        except:
            db5.rollback()  # 发生错误时回滚
            tk.messagebox.showerror(title='警告', message='数据库连接失败！')
        db5.close()  # 关闭数据库连接

        for i in range(1, 101):
            tree.insert("", "end", values=(str(i), state[i - 1]))

        tree.pack(fill='y')  # 纵向填充
        scrollBar.config(command=tree.yview)  # Treeview组件与垂直滚动条结合

    button = tk.Button(frame_ParkingInformation, text="停车场车位详细信息", font=("黑体", 15), bg="white", bd=3, width=25,\
                       command=parking_place)
    button.place(relx=0.36, rely=0.76)


"车辆进出历史记录"


def History():
    frame_History = tk.Frame(root, bg="white")
    frame_History.place(width=900, height=600)

    label_register = tk.Label(frame_History,
                              text="车辆进出历史记录",
                              font=("黑体", 25),
                              bg="white",
                              pady=20)
    label_register.pack()

    cols = ("c1", "c2", "c3", "c4", "c5")
    scrollBar = tk.Scrollbar(frame_History, orient='vertical')
    scrollBar.pack(side='right', fill='y')
    tree = Treeview(frame_History,
                    height=24,
                    show='headings',
                    columns=cols,
                    yscrollcommand=scrollBar.set)
    # 设置每列宽度和对齐方式
    tree.column("c1", width=240, anchor='center')
    tree.column("c2", width=120, anchor='center')
    tree.column("c3", width=200, anchor='center')
    tree.column("c4", width=120, anchor='center')
    tree.column("c5", width=120, anchor='center')
    # 设置每列表头标题文本
    tree.heading("c1", text="时间")
    tree.heading("c2", text="姓名")
    tree.heading("c3", text="车牌号码")
    tree.heading("c4", text="停车位编号")
    tree.heading("c5", text="状态")

    # 连接数据库
    db6 = pymysql.connect(host='localhost', user='root', password='hy1999721', database='parkinglotmanagement',\
                          charset="utf8")
    cursor6 = db6.cursor()  # 使用cursor()方法获取操作游标
    try:
        record = []
        cursor6.execute("select * from parking_lot")
        for row in cursor6.fetchall():
            if row[5] == 'FULL':
                record.append((str(row[6]), row[1], row[2], row[4], '进入'))
            else:
                record.append((str(row[6]), row[1], row[2], row[4], '进入'))
                record.append((str(row[7]), row[1], row[2], row[4], '离开'))
    except:
        db6.rollback()  # 发生错误时回滚
        tk.messagebox.showerror(title='警告', message='数据库连接失败！')
    db6.close()  # 关闭数据库连接

    for i in range(0, len(record)):
        tree.insert("", "end", values=record[i])

    tree.pack(fill='y')  # 纵向填充
    scrollBar.config(command=tree.yview)  # Treeview组件与垂直滚动条结合


'''菜单栏'''
menuBar = tk.Menu(root)
menuBar.add_command(label="车辆管理", command=CarManagement)
menuBar.add_command(label="信息查询", command=InformationSearch)
menuBar.add_command(label="当前停车场信息", command=ParkingInformation)
menuBar.add_command(label="车辆进出历史记录", command=History)
menuBar.add_command(label="退出系统", command=root.destroy)
# 显示菜单对象
root.config(menu=menuBar)

root.mainloop()